package com.eascs.web.o2o.scheduler.service;

import java.util.Date;

import javax.inject.Inject;

import org.springframework.dao.DataIntegrityViolationException;
import org.springframework.stereotype.Service;

import com.alibaba.dubbo.common.utils.StringUtils;
import com.alibaba.dubbo.config.annotation.Reference;
import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.app.log.Logger;
import com.eascs.app.log.LoggerFactory;
import com.eascs.web.o2o.scheduler.dao.BaseDao;
import com.eascs.web.o2o.scheduler.dao.CrmDao;
import com.eascs.web.o2o.scheduler.entity.CustomerAssetInfo;
import com.eascs.web.o2o.scheduler.entity.CustomerBasicInfo;
import com.eascs.web.o2o.scheduler.entity.CustomerExpansionInfo;
import com.eascs.web.o2o.scheduler.entity.CustomerShopInfo;
import com.eascs.web.o2o.scheduler.entity.CustomerUnit;
import com.eascs.web.o2o.scheduler.entity.EabaCustomer;
import com.eascs.web.o2o.scheduler.entity.HrEmployee;
import com.eascs.web.o2o.scheduler.util.Constant;
import com.eascs.workflow.common.entity.sequence.SequenceEntity;
import com.eascs.workflow.common.entity.sys.ActOrgDetail;
import com.eascs.workflow.common.service.sequence.SequenceService;
import com.eascs.workflow.common.service.sys.SysOrgService;
import com.github.stuxuhai.jpinyin.PinyinFormat;
import com.github.stuxuhai.jpinyin.PinyinHelper;

import jodd.util.StringUtil;

@Service
public class CrmService {

	protected final Logger logger = LoggerFactory.getLogger(getClass());
	@Inject
	CrmDao crmDao;
	@Inject
	BaseDao baseDao;

	@Reference
	private SysOrgService sysOrgService;

	@Reference
	private SequenceService sequenceService;

	/**
	 * 保存全部字段数据
	 * 
	 * @param sql
	 *            查询表语句
	 * @param requiredType
	 *            对象
	 * @param delSql
	 *            删除表语句
	 * 
	 */
	public <T> void loadData(String sql, Class<T> requiredType, String delSql) {
		int index = 0;
		int total = 0;
		while (index >= 0) {
			SqlPageQuery<T> sqlPageQuery = new SqlPageQuery<>(requiredType, Constant.PROCESS_DATA_LIMIT, index, sql);
			PageResult<T> pageResult = this.crmDao.findDataWithPage(sqlPageQuery);
			if (pageResult.getReturnDataSize() == 0)
				return;
			Integer count = pageResult.getDbRowCount();
			if (index == 0 && count > 0) {
				if (!"".equals(delSql)) {
					// 有数据先清空该表数据
					crmDao.deleteEntity(delSql);
				}
			}

			logger.debug("total:{},pagesize:{}", ++total, pageResult.getReturnDataSize());

			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {

				T obj = pageResult.getData().get(i);
				try {

					crmDao.saveEntity(obj);

				} catch (DataIntegrityViolationException e) {
					logger.error("数据库异常", e);
				}
			}

			if (pageResult.getPageTotal() > index + 1) {
				index++;
			} else {
				index = -1;
			}
		}

	}

	
	public <T> void loadOldAddCrmData(String sql) {
		int index = 0;
		int total = 0;
		while (index >= 0) {
			SqlPageQuery<CustomerBasicInfo> sqlPageQuery = new SqlPageQuery<>(CustomerBasicInfo.class, Constant.PROCESS_DATA_LIMIT, index, sql);
			PageResult<CustomerBasicInfo> pageResult = this.crmDao.findDataWithPage(sqlPageQuery);

			logger.debug("total:{},pagesize:{}", ++total, pageResult.getReturnDataSize());
			if (pageResult.getReturnDataSize() == 0)
				return;
			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				CustomerBasicInfo c = pageResult.getData().get(i);
				try {
					       //1. 保存客户数据
						   logger.info("[CRM]保存主申请人信息开始......");
						int num = crmDao.countMysql("select count(1) from customer_basic_info where  id_number=?", c.getIdNumber());
						if (num == 0){		
							crmDao.saveEntity(c);
						   logger.info("[CRM]保存主申请人信息结束......");
							// 2.保存配偶信息
							String findMateSql = "";
							if (c.getMateId() != null) {
								findMateSql = "SELECT l.loanapplyno AS customer_id, l.loanapplyno AS customer_code, l.crmotoloanersur AS customer_family_name, l.crmotoloaner AS customer_given_name, l.loanername AS customer_name, l.cctpertrnothertel AS customer_name_pinyin, l.age, l.sex, l.idcardtype AS id_type, l.idcard AS id_number, l.mobile AS mobile_number, y.customer_type AS customer_type, '' AS is_new_customer, '' AS number_of_loans, y.customerno AS customer_sd_no, y.customercode AS customer_sd, y.customername AS customer_sd_name, CASE WHEN (l.MARRCHILDREN = 'UMM') THEN 1 WHEN (l.MARRCHILDREN = 'MAR6') THEN 3 ELSE 2 END AS marital_status, 0 AS customer_source,  y.createdate AS create_date,  y.createdate AS modify_date FROM eadb.o2o_loaner l LEFT JOIN eadb.o2o_loanapply y ON l.loanapplyno = y.loanapplyno WHERE l.loanapplyno = ?";
								CustomerBasicInfo basicInfo = crmDao.getEntity(findMateSql, CustomerBasicInfo.class,c.getMateId());
								basicInfo.setMateId(c.getCustomerId());// 赋值配偶
								num = crmDao.countMysql("select count(1) from customer_basic_info  where id_number=?", basicInfo.getIdNumber());
								if (num == 0) {
									
									crmDao.saveEntity(basicInfo);
								} 
							} else {
								if (c.getMaritalStatus() == 2) {
									findMateSql = "SELECT l.loanapplyno AS customer_code, l.couplename AS customer_name, l.coupleage AS age, l.coupleidcardtype AS id_type, l.coupleidcard AS id_number, l.couplemobile AS mobile_number, l.loanapplyno AS mate_id, CASE WHEN (l.sex = 'F') THEN 'M' WHEN (l.sex = 'M') THEN 'F' END AS sex, CASE WHEN (l.MARRCHILDREN = 'UMM') THEN 1 WHEN (l.MARRCHILDREN = 'MAR6') THEN 3 ELSE 2 END AS marital_status, 0 AS customer_source, y.customer_type AS customer_type, y.customerno AS customer_sd_no, y.customercode AS customer_sd, y.customername AS customer_sd_name, y.createdate as create_date, y.createdate as modify_date FROM eadb.o2o_loaner l LEFT JOIN eadb.o2o_loanapply y ON l.loanapplyno = y.loanapplyno WHERE l.loanapplyno =? ";
									CustomerBasicInfo basicInfo = crmDao.getEntity(findMateSql, CustomerBasicInfo.class,c.getCustomerId());
									basicInfo.setMateId(c.getCustomerId());// 赋值配偶
									num = crmDao.countMysql("select count(1) from customer_basic_info where id_number=?",basicInfo.getIdNumber());
									if (num == 0) {
										crmDao.saveEntity(basicInfo);// 保存配偶信息
									}
									c.setMateId(basicInfo.getCustomerId());
									crmDao.excuteSql("update customer_basic_info set mate_id=? where customer_id=?",basicInfo.getCustomerId(), basicInfo.getMateId());// 修改主客户配偶
								}
							}
	
							// 2.更新顾客信息客户code
							logger.info("[CRM]更新顾客信息客户code开始......");
							String sql_code = "SELECT customer_code, customer_id FROM customer_basic_info WHERE customer_id = '"
									+ c.getCustomerId() + "'or mate_id = '" + c.getCustomerId() + "'";
							String expsql = "update customer_basic_info set customer_code=?  where customer_id=?";
							try {
								updateCustomerCode(sql_code, expsql, 1);
								logger.info("[CRM]更新顾客信息客户code结束......");
							} catch (Exception e) {
								e.printStackTrace();
								logger.error("[CRM]Error：更新顾客信息客户code失败！！！");
							}
	
							// 3.更新配偶姓名拼音、姓、名字
							try {
								logger.info("[CRM]更新配偶姓名拼音、姓、名字开始......");
								loadData2(
										"select * from customer_basic_info b where b.mate_id = '" + c.getCustomerId()
												+ "'and  b.customer_family_name is null and b.customer_name!='0' ",
										CustomerBasicInfo.class);
								logger.info("[CRM]更新配偶姓名拼音、姓、名字结束......");
							} catch (Exception e) {
								logger.error("[CRM]Error：更新配偶姓名拼音、姓、名字失败！！！");
								e.printStackTrace();
							}
	
							//4. 保存客户扩展信息
							logger.info("[CRM]保存主申请人扩展信息开始......");
							 num = crmDao.countMysql("select count(1) from customer_expansion_info where customer_id= ? ",c.getCustomerId());
							if (num == 0) {
								 findMateSql = "SELECT l.loanapplyno AS customer_expand_id, l.loanapplyno AS customer_id, l.province AS account_province, l.city AS account_city, l.addressprovince AS recipient_province, l.addresscity AS recipient_city, l.addresscounty AS recipient_county, l.nowaddress AS recipient_address, l.idauthority AS issuing_authority, l.idexpiredate AS license_expiration_date, CASE WHEN ( l.MARRCHILDREN NOT IN ('UMM', 'MAR2', 'MAR6')) THEN 1 ELSE 0 END AS have_children, CASE WHEN (l.MARRCHILDREN = 'MAR3') THEN 2 WHEN (l.MARRCHILDREN = 'MAR4') THEN 3 WHEN (l.MARRCHILDREN = 'MAR5') THEN 4 ELSE 1 END children_num, l.diploma AS education, l.telephone AS home_phone, l.occupation AS occupation, y.createdate AS create_date, y.createdate AS modify_date FROM eadb.o2o_loaner l LEFT JOIN eadb.o2o_loanapply y ON l.loanapplyno = y.loanapplyno  WHERE l.loanapplyno =? ";
								 CustomerExpansionInfo customerExpansionInfo = crmDao.getEntity(findMateSql, CustomerExpansionInfo.class,c.getCustomerId());
								 crmDao.saveEntity(customerExpansionInfo);
							}
							logger.info("[CRM]保存主申请人扩展信息结束......");
							//5. 保存配偶扩展信息
							logger.info("[CRM]保存主申请人配偶的扩展信息开始.......");
							CustomerExpansionInfo expansionInfo = null;
							if (StringUtil.isNotEmpty(c.getMateId())) {
								if (c.getMateId().length() < 10) {
									findMateSql = "SELECT l.loanapplyno AS customer_expand_id, l.loanapplyno AS customer_id, l.province AS account_province, l.city AS account_city, l.addressprovince AS recipient_province, l.addresscity AS recipient_city, l.addresscounty AS recipient_county, l.nowaddress AS recipient_address, l.idauthority AS issuing_authority, l.idexpiredate AS license_expiration_date, CASE WHEN ( l.MARRCHILDREN NOT IN ('UMM', 'MAR2', 'MAR6')) THEN 1 ELSE 0 END AS have_children, CASE WHEN (l.MARRCHILDREN = 'MAR3') THEN 2 WHEN (l.MARRCHILDREN = 'MAR4') THEN 3 WHEN (l.MARRCHILDREN = 'MAR5') THEN 4 ELSE 1 END children_num, l.diploma AS education, l.telephone AS home_phone, l.occupation AS occupation, y.createdate AS create_date, y.createdate AS modify_date FROM eadb.o2o_loaner l LEFT JOIN eadb.o2o_loanapply y ON l.loanapplyno = y.loanapplyno  WHERE l.loanapplyno =? ";
									 expansionInfo = crmDao.getEntity(findMateSql, CustomerExpansionInfo.class,c.getMateId());
								} else {
									findMateSql = "SELECT l.coupleoccupation AS occupation, l.coupleprovince AS account_province, l.couplecity AS account_city, l.couplenowaddress AS recipient_address, l.couplediploma AS education, CASE WHEN ( l.MARRCHILDREN NOT IN ('UMM', 'MAR2', 'MAR6')) THEN 1 ELSE 0 END AS have_children, CASE WHEN (l.MARRCHILDREN = 'MAR3') THEN 2 WHEN (l.MARRCHILDREN = 'MAR4') THEN 3 WHEN (l.MARRCHILDREN = 'MAR5') THEN 4 ELSE 1 END children_num, l.coupletelephone AS home_phone,y.createdate as create_date,y.createdate as modify_date FROM eadb.o2o_loaner l LEFT JOIN eadb.o2o_loanapply y ON l.loanapplyno = y.loanapplyno WHERE l.loanapplyno =?";
									expansionInfo = crmDao.getEntity(findMateSql, CustomerExpansionInfo.class,c.getCustomerId());
								}
								expansionInfo.setCustomerId(c.getMateId());
								num = crmDao.countMysql("select count(1) from customer_expansion_info where customer_id= ?",c.getMateId());
								if (num == 0) {
									crmDao.saveEntity(expansionInfo);
								}
							}
							logger.info("[CRM]保存主申请人配偶的扩展信息结束.......");
							//6.保存主申请人资产信息
							logger.info("[CRM]保存主申请人资产信息开始......");
							 num = crmDao.countMysql("select count(1) from customer_asset_info where customer_id= ? ",c.getCustomerId());
								if (num == 0) {
									 findMateSql = "SELECT l.loanapplyno AS customer_asset_id, l.loanapplyno AS customer_id, l.house AS own_estate_num, l.housered AS red_num, l.housemortgage AS mortgage_num, l.housemortgageterm AS estate_mortgage_term, l.housemonthrepayment AS estate_monthly_amount, l.housebalance AS estate_loan_over, l.car AS own_vehicles_num, l.carunsecured AS unsecured_num, l.carmortgage AS vehicle_mortgage_num, l.carmortgageterm AS vehicle_mortgage_term, l.carmonthrepayment AS vehicle_monthly_amount, l.carbalance AS vehicle_mortgage_over, l.loan AS bank_loan_items, l.loantotal AS loan_total, l.loanterm AS loan_term, l.loanmonthrepayment AS loan_monthly_repayment, l.loanbalance AS loan_over, l.otherloan AS other_loan, l.overdraft AS overdraft, l.lendersothervalue AS other_assets_value, l.legaldisputedesc AS legal_disputes_info, l.islegaldispute AS is_legal_disputes, l.creditcard AS credit_num, l.otherloantotal AS borrow_total FROM eadb.o2o_loaner l   WHERE l.loanapplyno =?";
									 CustomerAssetInfo  customerAssetInfo = crmDao.getEntity(findMateSql, CustomerAssetInfo.class,c.getCustomerId());
									 crmDao.saveEntity(customerAssetInfo);
								}
						   logger.info("[CRM]保存主申请人资产信息结束......");
						   //7.保存配偶的资产信息
							logger.info("[CRM]保存配偶的资产信息开始......");
							if(StringUtils.isNotEmpty((c.getMateId()))&&c.getMateId().length() < 10){
							    num = crmDao.countMysql("select count(1) from customer_asset_info where customer_id= ? ",c.getMateId());
								if (num == 0) {
									 findMateSql = "SELECT l.loanapplyno AS customer_asset_id, l.loanapplyno AS customer_id, l.house AS own_estate_num, l.housered AS red_num, l.housemortgage AS mortgage_num, l.housemortgageterm AS estate_mortgage_term, l.housemonthrepayment AS estate_monthly_amount, l.housebalance AS estate_loan_over, l.car AS own_vehicles_num, l.carunsecured AS unsecured_num, l.carmortgage AS vehicle_mortgage_num, l.carmortgageterm AS vehicle_mortgage_term, l.carmonthrepayment AS vehicle_monthly_amount, l.carbalance AS vehicle_mortgage_over, l.loan AS bank_loan_items, l.loantotal AS loan_total, l.loanterm AS loan_term, l.loanmonthrepayment AS loan_monthly_repayment, l.loanbalance AS loan_over, l.otherloan AS other_loan, l.overdraft AS overdraft, l.lendersothervalue AS other_assets_value, l.legaldisputedesc AS legal_disputes_info, l.islegaldispute AS is_legal_disputes, l.creditcard AS credit_num, l.otherloantotal AS borrow_total FROM eadb.o2o_loaner l   WHERE l.loanapplyno =?";
									 CustomerAssetInfo  customerAssetInfo = crmDao.getEntity(findMateSql, CustomerAssetInfo.class,c.getMateId());
									 if(customerAssetInfo!=null){
									    crmDao.saveEntity(customerAssetInfo);
									 }
								}
							}
						   logger.info("[CRM]保存配偶的资产信息结束......");
						   
						 //8.保存主申请人门店信息
							logger.info("[CRM]保存主申请人门店信息开始......");
							 num = crmDao.countMysql("select count(1) from customer_shop_info where customer_id= ? ",c.getCustomerId());
								if (num == 0) {
									 findMateSql = "SELECT p.loanshopno AS customer_shop_id, p.loanapplyno AS customer_id, p.shoptype, p.shopname, p.setupdate AS stupdate, p.telephone, p.officeaddress AS officeaddress, p.shopaddress AS shopaddress, p.shareholders AS shareholders, p.controllername AS controllername, p.businessarea AS businessarea, p.businesshours AS businesshours, p.shoprent AS shoprent, p.utilitybills AS utilitybills, p.fixedsalary AS fixedsalary, p.mainproducts AS mainproducts, p.shopstock AS shoptock, p.receivable AS receivable, p.payables AS payables, p.eacooperationdate AS eacooperationdate, p.eapurchaseamount AS eapurchaseamount, p.suppliername1 AS suppliername1, p.suppliercooperationdate1 AS suppliercooperationdate1, p.monthamount1 AS monthamount1, p.suppliername2 AS suppliername2, p.suppliercooperationdate2 AS suppliercooperationdate2, p.monthamount2 AS monthamount2, p.suppliername3 AS suppliername3, p.suppliercooperationdate3 AS suppliercooperationdate3, p.monthamount3 AS monthamount3, p.turnover1th, p.turnover2th, p.turnover3th, p.turnover4th, p.turnover5th, p.turnover6th, p.assetsliabilities, p.operatorname, p.shopno, p.isgetdata, p.shopcode, p.islock, p.storeyears AS storyears, p.iscontroller, p.storeinvamount, p.officeprovince, p.officecity, p.officecounty, p.shopprovince, p.shopcity, p.shopcounty, p.vocation FROM eadb.o2o_loanshop p WHERE p.loanapplyno =?";
									 CustomerShopInfo  customerShopInfo = crmDao.getEntity(findMateSql, CustomerShopInfo.class,c.getCustomerId());
									 if(customerShopInfo!=null){
										 crmDao.saveEntity(customerShopInfo);
									  }
								}
						   logger.info("[CRM]保存主申请人门店信息结束......");
						   //9.保存配偶的门店信息
							logger.info("[CRM]保存配偶的门店信息开始......");
							if(StringUtils.isNotEmpty((c.getMateId()))&&c.getMateId().length() < 10){
							 num = crmDao.countMysql("select count(1) from customer_shop_info where customer_id= ? ",c.getMateId());
								if (num == 0) {
									 findMateSql = "SELECT p.loanshopno AS customer_shop_id, p.loanapplyno AS customer_id, p.shoptype, p.shopname, p.setupdate AS stupdate, p.telephone, p.officeaddress AS officeaddress, p.shopaddress AS shopaddress, p.shareholders AS shareholders, p.controllername AS controllername, p.businessarea AS businessarea, p.businesshours AS businesshours, p.shoprent AS shoprent, p.utilitybills AS utilitybills, p.fixedsalary AS fixedsalary, p.mainproducts AS mainproducts, p.shopstock AS shoptock, p.receivable AS receivable, p.payables AS payables, p.eacooperationdate AS eacooperationdate, p.eapurchaseamount AS eapurchaseamount, p.suppliername1 AS suppliername1, p.suppliercooperationdate1 AS suppliercooperationdate1, p.monthamount1 AS monthamount1, p.suppliername2 AS suppliername2, p.suppliercooperationdate2 AS suppliercooperationdate2, p.monthamount2 AS monthamount2, p.suppliername3 AS suppliername3, p.suppliercooperationdate3 AS suppliercooperationdate3, p.monthamount3 AS monthamount3, p.turnover1th, p.turnover2th, p.turnover3th, p.turnover4th, p.turnover5th, p.turnover6th, p.assetsliabilities, p.operatorname, p.shopno, p.isgetdata, p.shopcode, p.islock, p.storeyears AS storyears, p.iscontroller, p.storeinvamount, p.officeprovince, p.officecity, p.officecounty, p.shopprovince, p.shopcity, p.shopcounty, p.vocation FROM eadb.o2o_loanshop p WHERE p.loanapplyno =?";
									 CustomerShopInfo  customerShopInfo = crmDao.getEntity(findMateSql, CustomerShopInfo.class,c.getMateId());
									 if(customerShopInfo!=null){
									    crmDao.saveEntity(customerShopInfo);
									 }
								}
							}
						   logger.info("[CRM]保存配偶的门店信息结束......");
					}
				} catch (Exception e) {
					logger.error("数据库异常", e);
				}
			}

			if (pageResult.getPageTotal() > index + 1) {
				index++;
			} else {
				index = -1;
			}
		}

	}

	/**
	 * 更改客户配置的姓，名和拼音
	 * 
	 * @param sql
	 *            查询表语句
	 * @param requiredType
	 *            对象
	 * @param delSql
	 *            删除表语句
	 * 
	 */
	public <T> void loadData2(String sql, Class<CustomerBasicInfo> c) {
		int index = 0;
		int total = 0;
		while (index >= 0) {
			SqlPageQuery<CustomerBasicInfo> sqlPageQuery = new SqlPageQuery<CustomerBasicInfo>(c,
					Constant.PROCESS_DATA_LIMIT, index, sql);
			PageResult<CustomerBasicInfo> pageResult = this.crmDao.findDataWithPageForMysql(sqlPageQuery);

			logger.debug("total:{},pagesize:{}", ++total, pageResult.getReturnDataSize());
			if (pageResult.getReturnDataSize() == 0)
				return;

			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				CustomerBasicInfo obj = pageResult.getData().get(i);

				try {
					String customerName = obj.getCustomerName();
					String firstName = "";
					String lastName = "";
					if (StringUtil.isNotBlank(customerName) && !"1".equals(customerName) && !"0".equals(customerName)) {

						if (customerName.length() <= 3) {
							customerName = customerName.trim();
							firstName = customerName.substring(0, 1);
							lastName = customerName.substring(1, customerName.length());

						} else {
							customerName = customerName.trim();
							firstName = customerName.substring(0, 2);
							lastName = customerName.substring(2, customerName.length());

						}

						String firstNameEnglish = PinyinHelper.convertToPinyinString(firstName, "",
								PinyinFormat.WITHOUT_TONE);
						String lastNameEnglish = PinyinHelper.convertToPinyinString(lastName, "",
								PinyinFormat.WITHOUT_TONE);
						String allEnglistName = firstNameEnglish + " " + lastNameEnglish;
						allEnglistName = allEnglistName.toUpperCase();
						String updateSql = "update customer_basic_info set customer_family_name=? ,customer_given_name=? ,customer_name_pinyin=? where customer_id=? ";
						crmDao.excuteSql(updateSql, firstName, lastName, allEnglistName, obj.getCustomerId());
					} else {
						logger.error("客户id：[{}]姓名为空", obj.getCustomerId());
					}
				} catch (DataIntegrityViolationException e) {
					logger.error("数据库异常", e);

				}
			}
		}

	}

	/**
	 * @Title: updateCustomerMate
	 * @Description:添加配偶信息
	 * @author 张庆奇
	 * @param sql
	 * @param findSql
	 * @param expSql
	 * @param flag
	 *            1,基本信息，2扩展信息
	 * @return void
	 * @throws @date
	 *             2016年8月30日 下午4:51:17
	 */
	public void updateCustomerMate(String sql, String findSql, String expSql, int flag) {
		int index = 0;
		int total = 0;
		while (index >= 0) {

			SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, Constant.PROCESS_DATA_LIMIT, index, sql);
			PageResult<Object[]> pageResult = this.crmDao.findDataWithPageForMysql(sqlPageQuery);

			logger.debug("total:{},pagesize:{}", ++total, pageResult.getReturnDataSize());
			if (pageResult.getReturnDataSize() == 0)
				return;
			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				Object[] objs = pageResult.getData().get(i);

				try {
					if (flag == 1) {
						CustomerBasicInfo entity = (CustomerBasicInfo) crmDao.getEntity(findSql,
								CustomerBasicInfo.class, objs[0]);
						entity.setMateId(objs[0].toString());
						crmDao.saveEntity(entity);// 添加配偶信息
						crmDao.excuteSql(expSql, entity.getCustomerId(), objs[0]);// 更新配偶id
					} else {
						CustomerExpansionInfo entity = (CustomerExpansionInfo) crmDao.getEntity(findSql,
								CustomerExpansionInfo.class, objs[1]);
						entity.setCustomerId(objs[0].toString());
						entity.setCreateDate(new Date());
						entity.setModifyDate(new Date());
						crmDao.saveEntity(entity);// 添加配偶信息

					}
				} catch (Exception e) {
					logger.error("主键", objs[0]);
					logger.error("添加配偶信息，并且更新配偶id,数据库异常", e);

				}
			}

		}

	}

	/**
	 * @Title: updateCustomerCode
	 * @Description: 更新客户code
	 * @author 张庆奇
	 * @param sql
	 * @param exeSql
	 *            param flag 0的时候需要清空, 1:不需要清空customer_unit表
	 * @return void
	 * @throws @date
	 *             2016年7月5日 上午11:36:33
	 */
	public void updateCustomerCode(String sql, String exeSql, int flag) {
		int index = 0;
		int total = 0;
		while (index >= 0) {

			SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, Constant.PROCESS_DATA_LIMIT, index, sql);
			PageResult<Object[]> pageResult = this.crmDao.findDataWithPageForMysql(sqlPageQuery);
			if (pageResult.getReturnDataSize() == 0)
				return;
			Integer count = pageResult.getDbRowCount();
			if (index == 0 && count > 0) {
				if (flag == 0) {
					// 有数据先清空该表数据
					crmDao.deleteEntity("delete from customer_unit");
				}
			}

			logger.debug("total:{},pagesize:{}", ++total, pageResult.getReturnDataSize());

			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				Object[] objs = pageResult.getData().get(i);

				try {

					if (!objs[0].toString().contains("-")) {
						String customerCode = getCustomerCode(objs[0].toString(), objs[1].toString());
						if (StringUtil.isNotBlank(customerCode)) {
							crmDao.excuteSql(exeSql, customerCode, objs[1]);
							logger.info("[CRM]更新顾客customerCode,customerCode[{}], customer_id[{}]", customerCode,
									objs[1].toString());
						} else {
							logger.info("[CRM]更新顾客customerCode字段失败,获取customerCode为空, customer_id[{}]",
									objs[1].toString());
						}
					}

				} catch (Exception e) {
					logger.error("主键", objs[1]);
					logger.error("更新客户code,数据库异常", e);

				}
			}

			
		}

	}

	/**
	 * 当有部分客户ID没有生成的时候，调用此方法，重新去更新code
	 * 
	 * @param sql
	 * @param exeSql
	 *            2016年7月20日
	 */
	public void updateCustomerCodeOnly(String sql, String exeSql) {
		int index = 0;
		int total = 0;
		while (index >= 0) {

			SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, Constant.PROCESS_DATA_LIMIT, index, sql);
			PageResult<Object[]> pageResult = this.crmDao.findDataWithPageForMysql(sqlPageQuery);

			logger.debug("total:{},pagesize:{}", ++total, pageResult.getReturnDataSize());
			if (pageResult.getReturnDataSize() == 0)
				return;
			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				Object[] objs = pageResult.getData().get(i);

				try {
					String customerCode = getCustomerCodeOnly(objs[0].toString());
					crmDao.excuteSql(exeSql, customerCode, objs[0]);

				} catch (Exception e) {
					logger.error("主键", objs[1]);
					logger.error("更新客户code,数据库异常", e);

				}
			}

			if (pageResult.getPageTotal() > index + 1) {
				index++;
			} else {
				index = -1;
			}
		}

	}

	/**
	 * @插入 sql
	 */
	public void excute(String sql,Object... params) {

		crmDao.excuteSql(sql,params);

	}

	/**
	 * @Title: getCustomerCode
	 * @Description: 得到客户编码，并且同步客户与平台管理表
	 * @author 张庆奇
	 * @param loanapplyno
	 * @param customer_id
	 * @return
	 * @return String
	 * @throws @date
	 *             2016年7月5日 上午11:16:27
	 */
	@SuppressWarnings("unused")
	public String getCustomerCode(String loanapplyno, String customerId) {
		String customerCode = "";
		// 1.根据loanapplyno找到经办人的组织id
		String sql = "select t.ltd from eadb.o2o_loanapply t where loanapplyno=? ";
		//配偶信息和主申请人信息一致
		if (loanapplyno.length() > 10) {
			CustomerBasicInfo crm = crmDao.findById(CustomerBasicInfo.class, loanapplyno);
			if (crm != null && StringUtils.isNotEmpty(crm.getMateId())) {
				loanapplyno = crm.getMateId();
			}
		}
		String info = baseDao.findString(sql, loanapplyno);
		if (info != null) {
			// 2.根据组织id,找到组织code，name
			ActOrgDetail actOrgDetail = sysOrgService.getOrgDetailByUnitNo(info);
			logger.info("生成组织信息，接口参数：[{}],返回值:[{}],[{}]", info, actOrgDetail,
					actOrgDetail.getProvinceLetterCode());
			if (actOrgDetail != null) {

				int num = crmDao.countMysql("select count(1) from customer_unit  where customer_id= ?",customerId);

				// 3.保存数据
				CustomerUnit unit = new CustomerUnit();
				unit.setCustomerId(customerId);
				unit.setUnitId(actOrgDetail.getUnitNo());
				unit.setUnitCode(actOrgDetail.getUnitCode());
				unit.setUnitName(actOrgDetail.getOrgName());
				unit.setOpTime(new Date());
				if (num == 0) {
					crmDao.saveEntity(unit);
				} else {
					crmDao.excuteSql("delete from customer_unit where customer_id= ?",customerId);
					crmDao.saveEntity(unit);
				}
				// 4.生成客户编码

				String prefix = actOrgDetail.getProvinceLetterCode() + "-" + actOrgDetail.getUnitCode();
				customerCode = sequenceService.getSeq(SequenceEntity.CRM_CUSTOMER_NO, 5, prefix);
				if (StringUtil.isBlank(customerCode)) {
					logger.info("[CRM SERVICE]从workflow获取的customerCode为[{}], prefix[{}]", customerCode, prefix);
				}

			} else {
				logger.info("[CRM SERVICE]从workflow中未查询到组织ID为[{}]的ActOrgDetail", info);
			}
		} else {
			logger.info("[CRM SERVICE]从workflow中未查询到loanapplyno[{}]的Employee", loanapplyno);
		}

		return customerCode;
	}

	public String getCustomerCodeOnly(String loanapplyno) {
		String customerCode = "";
		// 1.根据loanapplyno找到经办人的组织id
		String sql = "select e.operationdept from eadb.hr_employee e where e.employeeno = (select t.operatorno from eadb.o2o_loanapply t where loanapplyno=? )";
		HrEmployee info = crmDao.getEntity(sql, HrEmployee.class, loanapplyno);
		if (info != null) {
			// 2.根据组织id,找到组织code，name
			ActOrgDetail actOrgDetail = sysOrgService.getOrgDetailByUnitNo(info.getOperationdept().toString());
			if (actOrgDetail != null) {
				// 4.生成客户编码
				String prefix = actOrgDetail.getProvinceLetterCode() + "-" + actOrgDetail.getUnitCode();
				customerCode = sequenceService.getSeq(SequenceEntity.CRM_CUSTOMER_NO, 5, prefix);
			}
		}

		return customerCode;
	}

	public void updateCustomerCodeById(String sql, String customerId) {
		String customerCode = getCustomerCodeOnly(customerId);
		crmDao.excuteSql(sql, customerCode, customerId);
	}

	/**
	 * @Title: loadEabaCustomerData
	 * @Description: 从oa系统同步门店信息到crm系统里面去
	 * @author 张庆奇
	 * @param sql
	 * @return void
	 * @throws @date
	 *             2016年11月29日 上午10:08:54
	 */
	@Deprecated
	public <T> void loadEabaCustomerData(String sql) {
		int index = 0;
		int total = 0;
		while (index >= 0) {
			SqlPageQuery<EabaCustomer> sqlPageQuery = new SqlPageQuery<>(EabaCustomer.class,
					Constant.PROCESS_DATA_LIMIT, index, sql);
			PageResult<EabaCustomer> pageResult = this.crmDao.findDataWithPage(sqlPageQuery);

			logger.debug("total:{},pagesize:{}", ++total, pageResult.getReturnDataSize());
			if (pageResult.getReturnDataSize() == 0)
				return;
			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {

				EabaCustomer obj = pageResult.getData().get(i);
				try {
					int num = crmDao.countMysql("select count(1) from eaba_customer where cno= ? ", obj.getCno());
					if (num == 0) {
						crmDao.saveEntity(obj);
					}

				} catch (Exception e) {
					logger.error("数据库异常", e);
				}
			}

			if (pageResult.getPageTotal() > index + 1) {
				index++;
			} else {
				index = -1;
			}
		}

	}
}
